﻿--1. set khóa cho bảng advanceVoucher
ALTER TABLE dbo.tblAdvanceVoucher ADD CONSTRAINT
	PK_tblAdvanceVoucher PRIMARY KEY CLUSTERED 
	(
	ID_AdvanceVoucher
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--2. Set khóa cho bảng contfee
ALTER TABLE dbo.tblContFee ADD CONSTRAINT
	PK_tblContFee PRIMARY KEY CLUSTERED 
	(
	ID_ContFee
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO


USE [TanNamChinh]
GO
/****** Object:  StoredProcedure [dbo].[GetPriceOfFile]    Script Date: 11/04/2013 09:54:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetPriceOfFile] ( @id_feeType AS INT, @id_customer AS int)
AS
BEGIN
--	  SELECT  BUYING ,EBS,THC, BILL, SEAL, TLX, OTHER, BUYING +EBS+THC+BILL+SEAL+TLX+OTHER AS TOTAL
--				FROM
--				(		SELECT tff.ID_File,tf.FeeName, tff.Price  FROM tblFileFee tff, tblFee tf
--						WHERE tff.ID_Fee = tf.ID_Fee AND tf.ID_FeeType = 1
--						GROUP BY tff.ID_File,TF.ID_Fee,tf.FeeName, tff.Price
--					) AS tSource
--				PIVOT
--				(
--					SUM(Price)
--					FOR FeeName IN (BUYING ,EBS,THC, BILL, SEAL, TLX, OTHER  )
--				) AS tPivoted 

--			select id_file,fileno,CustomerName ,
--			  coalesce(BUYING, 0) BUYING,
--			  coalesce(EBS, 0) EBS,
--			  coalesce(THC, 0) THC,
--			  coalesce(BILL, 0) BILL,
--			  COALESCE(SEAL,0) SEAL,
--			  COALESCE(TLX,0) TLX,
--			  COALESCE(OTHER,0) OTHER
--			from 
--			(
--                SELECT tff.ID_File,tf2.FileNo, tc.CustomerName, tf.FeeName, tff.Price 
--                  FROM tblFileFee tff, tblFee tf, tblFile tf2, tblCustomer tc
--						WHERE tff.ID_Fee = tf.ID_Fee 
--						AND tf.ID_FeeType =  @id_feeType  
--						AND tf2.ID_File = tff.ID_File
--						AND tf2.ID_Customer =   @id_customer  
--						AND tc.ID_Customer = tf2.ID_Customer
--						GROUP BY tff.ID_File,tf2.FileNo,tc.CustomerName ,TF.ID_Fee,tf.FeeName, tff.Price
--			) d
--			pivot
--			(
--			  sum(price)
--			  for feename in (BUYING ,EBS,THC, BILL, SEAL, TLX, OTHER)
--			) piv;

DECLARE @cols AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FEENAME) 
                      FROM tblFee tf WHERE tf.ID_FeeType = @id_feeType
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        --PRINT @cols

select @colsNull = STUFF((SELECT distinct ', Coalesce(' + QUOTENAME(feename) +', 0) as '+ QUOTENAME(feename)
                    from tblfee WHERE ID_FeeType = @id_feeType
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
--PRINT @colsNull
IF	@id_customer !=0 
set @query = 'SELECT ID_File,FileNo,CustomerName,' + @colsNull + ' 
            from 
            (
                SELECT tff.ID_File,tf2.FileNo,tc.CustomerName,tf.FeeName, tff.Price  FROM tblFileFee tff, tblFee tf, tblFile tf2, tblCustomer tc
									WHERE tff.ID_Fee = tf.ID_Fee AND tf.ID_FeeType = ' + CAST(@id_feeType AS NVARCHAR(50)) + 'AND tf2.ID_File = tff.ID_File
									AND tf2.ID_Customer = ' + CAST(@id_customer AS NVARCHAR(50)) + 'AND tc.ID_Customer = tf2.ID_Customer
									GROUP BY tff.ID_File,tf2.FileNo,tc.CustomerName, TF.ID_Fee,tf.FeeName, tff.Price
            ) d
            pivot 
            (
                sum(price)
                for feename in (' + @cols + ')
            ) p '
ELSE
	set @query = 'SELECT ID_File,FileNo,CustomerName,' + @colsNull + ' 
            from 
            (
                SELECT tff.ID_File,tf2.FileNo,tc.CustomerName, tf.FeeName, tff.Price  FROM tblFileFee tff, tblFee tf, tblFile tf2, tblCustomer tc
									WHERE tff.ID_Fee = tf.ID_Fee AND tf.ID_FeeType = ' + CAST(@id_feeType AS NVARCHAR(50)) + 'AND tf2.ID_File = tff.ID_File 
									AND tc.ID_Customer = tf2.ID_Customer
									GROUP BY tff.ID_File,tf2.FileNo,tc.CustomerName, TF.ID_Fee,tf.FeeName, tff.Price
            ) d
            pivot 
            (
                sum(price)
                for feename in (' + @cols + ')
            ) p '
execute(@query);
END
	